package com.wlgc.mapper;

import com.wlgc.entity.Assigment.Assignment;
import com.wlgc.entity.Assigment.Student_Assigment;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AssignmentMapper {


    //查询学生个人信息
    @Select("select * from allAssignment where sid=#{sid} and cid=#{cid} and isFinish=#{isFinish}")
    List<Student_Assigment> getCourseById(@Param("sid") int sid, @Param("cid") int cid, @Param("isFinish") String isFinish);

    //管理员查询学生信息
    @Select("select * from allAssignment where sid=#{sid} and cid=#{cid}")
    List<Student_Assigment> getAllCourseById(@Param("sid") int sid, @Param("cid") int cid);

    //通过作业名查询作业号
    @Select("select aid from assignment where name=#{name}")
    int getAidByAssignmentName(String name);


    //完成作业修改信息
    @Update("update sa set isFinish ='完成' where sid=#{sid} and aid=#{aid}")
    void finishAssignment(@Param("sid") int sid, @Param("aid") int aid);


    @Select("select detail from assignment where aid =#{aid}")
    String getDetail(@Param("aid") int aid);


    //获取对应课程作业全部完成的学生
    @Results({
            @Result(column = "未完成", property = "isFinish"),
    })
    @Select("select distinct sid,studentName,'未完成' from allAssignment where cno=#{cno} and cid=#{cid} and sid in(select distinct sid from allAssignment where isFinish='未完成')")
    List<Student_Assigment> getClassCourseUF(@Param("cno") int cno, @Param("cid") int cid);

    @Results({
            @Result(column = "完成", property = "isFinish"),
    })
    @Select("select distinct sid,studentName,'完成' from allAssignment where cno=#{cno} and cid=#{cid} and sid not in(select distinct sid from allAssignment where isFinish='未完成')")
    List<Student_Assigment> getClassCourseF(@Param("cno") int cno, @Param("cid") int cid);

    //通过课程所有的作业
    @Select("select  aid,name as assignmentName,detail from assignment where aid in (select distinct aid from allAssignment where courseName=#{courseName} and cno=#{cno}) ")
    public List<Student_Assigment> getCourseAllAssignmentByCourseName(@Param("courseName") String courseName, @Param("cno") int cno);

    //添加作业
    @Insert("insert into assignment(name,detail) values(#{name},#{detail})")
    public int addAssignment(@Param("name") String name, @Param("detail") String detail);
    //添加作业和课程的关系
    @Insert("insert into course_assignment(cid,aid) values(#{cid},#{aid})")
    public int addCourse_Assignment(int cid, int aid);
    //将作业添加给指定班级的学生
    @Insert("insert into student_assignment(sid,aid) select sid,#{aid} from student_class where cno=#{cno}")
    public int addStudent_Assignment(int aid,int cno);
    //将作业添加到所有有某课程的班级
    @Insert("insert into student_assignment(sid,aid) select sid,#{aid} from student_class where cno in (select distinct" +
            " cno from student_course_view where cid=(select cid from course_assignment where aid=#{aid}))")
    public int addStudent_Assignment_All(int aid);

}
